<?php

namespace app\index\controller;

use think\Controller;
use Think;
use think\Db;
use app\v300\model\Store;
use app\v300\model\Item;

class PhpexcelController extends Controller {

//http://www.thinkphp.cn/topic/14005.html
    public function index() {
        return view('index');
    }

    /*
     * 导入excel数据
     * author wxf
     * date 2017-04-14
     */

    function impUser() {
        if (!empty($_FILES)) {
            header("Content-Type:text/html;charset=utf8");

            $file = request()->file('import');
            $fileInfo = $file->move(ROOT_PATH . '../kkmimg/sv300/phpexcel', date('Ymdhis'));
//            echo "<pre/>";
//            print_r($fileInfo['name']);
            // 上传文件
            if (!$fileInfo) {
                $this->error('上传失败');
            }
            $fileName1 = 'sv300/phpexcel/' . str_replace(DS, '/', $fileInfo->getSaveName());
            $fileName = explode('.', $fileName1);
            //$fileName1="http://127.0.0.1/kkmimg/".$fileName1;
            $fileName1 = ROOT_PATH . '../kkmimg/' . $fileName1;
            //print_r($fileName1);

            Vendor("PHPExcel.IOFactory");
            if ($fileName[1] == 'xls') {
                 $reader = \PHPExcel_IOFactory::createReader('Excel5'); //设置以Excel2007格式  
            }elseif ($fileName[1] == 'xlsx') {
                 $reader = \PHPExcel_IOFactory::createReader('Excel2007'); //设置以Excel2007格式  
            }
            $PHPExcel = $reader->load($fileName1); // 载入excel文件  
            $sheet = $PHPExcel->getSheet(0); // 读取第一個工作表  
            $highestRow = $sheet->getHighestRow(); // 取得总行数  
            $highestColumn = $sheet->getHighestColumn(); // 取得总列数  
            print_r($highestRow);

//
//
//
//            vendor("PHPExcel.PHPExcel");
//            // 判断Excel文件扩展名
//            if ($fileName[1] == 'xls') {
//                import("vendor.PHPExcel.Reader.Excel5");
//                $objReader = new \PHPExcel_Reader_Excel5();
//            } else if ($fileName[1] == 'xlsx') {
//                import("vendor.PHPExcel.Reader.Excel2007");
//                $objReader = new \PHPExcel_Reader_Excel2007();
//            }
//            //$objReader = \PHPExcel_IOFactory::createReader('Excel5');
//            $objPHPExcel = $objReader->load($fileName, $encode = 'utf-8');
//            $sheet = $objPHPExcel->getSheet(0); // 获取表中的第一个工作表，如果要获取第二个把0改为1，依次类推
//            $highestRow = $sheet->getHighestRow(); // 取得总行数
//            $highestColumn = $sheet->getHighestColumn(); // 取得总列数
            // 循环获取表中的数据，$currentRow表示当前行，从那行开始读取数据，索引值为0开始
            for ($currentRow = 3; $currentRow <= $highestRow; $currentRow++) {
                // 从哪开始A标识第1列
                for ($currentColumn = 'A'; $currentColumn != $highestColumn; $currentColumn++) {
                    // 数据坐标
                    $address = $currentColumn . $currentRow;
                    // 获取到数据保存到$arr中
                    $sourceData[$currentRow][$currentColumn] = $sheet->getCell($address)->getValue();
                }
            }

            //查询是否审核通过
            for ($i = 3; $i < count($sourceData) + 3; $i++) {
                $phone = $sourceData[$i]['F'];
                if ($phone) {
                    //$data = Store::where("keeper_phone=" . $phone." and is_del=1")->find();
                    $data = Store::where('keeper_phone','=',$phone)->where('is_del','=',1)->field('id,auditstore')->find();
                    if ($data['auditstore'] == 1) {
                        $item=  Item::where('store_id','=',$data['id'])->where('is_del','=',1)->count();
                        $sourceData[$i]['L'] = '是';
                        $sourceData[$i]['M'] = $item;
                    } else {
                        $sourceData[$i]['L'] = '否';
                        $sourceData[$i]['M'] = 0;
                    }
                } else {
                    $sourceData[$i]['L'] = '无';
                    $sourceData[$i]['M'] = 0;
                }
            }

            $xlsName = "大卫动力开会登记表";
            $this->exportExcel($xlsName, $sourceData);
//            echo "<pre/>";
//            print_r($sourceData);
//            die;
            $this->success('导入成功！');
        } else {
            $this->error("请选择上传的文件");
        }
    }

    /*
     * 导出excel
     * author wxf
     * date 2017-04-14
     */

    public function exportExcel($expTitle, $expTableData) {
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle); //文件名称
        $fileName = $xlsTitle . date('_YmdHis'); //or $xlsTitle 文件名称可根据自己情况设定
        $ii = 3;
        Vendor('PHPExcel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[13 - 1] . '1'); //合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle . '  Export time:' . date('Y-m-d H:i:s'));
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(//居中加粗
                array(
                    'font' => array(
                        'bold' => true
                    ),
                    'alignment' => array(
                        'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER
                    )
                )
        );
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30); // 第一行的默认高度
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(25); // 第一行的默认高度
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20); // 所有单元格默认高度
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(16); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(16); //设置宽width
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //垂直居中
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                    'style' => \PHPExcel_Style_Border::BORDER_THIN, //细边框  
                //'color' => array('argb' => 'FFFF0000'),  
                ),
            ),
        );
        $objPHPExcel->getActiveSheet()->getStyle('A1:M' . (count($expTableData) + 2))->applyFromArray($styleArray); //这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪个格就把这个N改为那个字母替代  


        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2', "客户姓名");
        $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B2', "门票类型");
        $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C2', "门票金额");
        $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D2', "门票数量");
        $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E2', "支付方式");
        $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F2', "联系方式");
        $objPHPExcel->getActiveSheet()->getStyle('F2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G2', "3万总裁课");
        $objPHPExcel->getActiveSheet()->getStyle('G2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H2', "落地课");
        $objPHPExcel->getActiveSheet()->getStyle('H2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I2', "业务员");
        $objPHPExcel->getActiveSheet()->getStyle('I2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J2', "克克美审核");
        $objPHPExcel->getActiveSheet()->getStyle('J2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K2', "备注");
        $objPHPExcel->getActiveSheet()->getStyle('K2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L2', "克克美审核备注");
        $objPHPExcel->getActiveSheet()->getStyle('L2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M2', "店铺项目总数");
        $objPHPExcel->getActiveSheet()->getStyle('M2')->applyFromArray(array('font' => array('bold' => true)));

        foreach ($expTableData as $key => $val) {
            $tt = 'A' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, " " . $val['A']); //加  “ ”防止用科学计数法，中间必须有空格，否则无效
            $tt = 'B' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, " " . $val['B']);
            $tt = 'C' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['C']);
            $tt = 'D' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['D']);
            $tt = 'E' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['E']);
            $tt = 'F' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, " " . $val['F']);
            $tt = 'G' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['G']);
            $tt = 'H' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['H']);
            $tt = 'I' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['I']);
            $tt = 'J' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['J']);
            $tt = 'K' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['K']);
            $tt = 'L' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['L']);
            $tt = 'M' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['M']);
            $ii++;
        }
        ob_end_clean();
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Type: application/vnd.ms-excel;charset=utf-8');
        header('Content-Disposition: attachment;filename="' . $fileName . '.xls"');  //日期为文件名后缀
        header('Cache-Control: max-age=0');
        header("Content-type:application/octet-stream");
        header("Accept-Ranges:bytes");
        header("Pragma: no-cache");
        header("Expires: 0");
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    public function aa() {
        $xlsName = "大卫动力开会登记表";
        $sourceData = [['A' => 1, 'B' => 2, 'C' => 3, 'D' => 4, 'E' => 5, 'F' => 6, 'G' => 7, 'H' => 8, 'I' => 9, 'J' => 10, 'K' => 11, 'L' => 12], ['A' => 1, 'B' => 2, 'C' => 3, 'D' => 4, 'E' => 5, 'F' => 6, 'G' => 7, 'H' => 8, 'I' => 9, 'J' => 10, 'K' => 11, 'L' => 12]];
        $this->exportExcel($xlsName, $sourceData);
    }

}
